﻿IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FUBConcatenateSplitString]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
	DROP FUNCTION [dbo].[FUBConcatenateSplitString]
END
GO


-- SELECT dbo.FUBConcatenateSplitString ('A, B, C', ',', '@', '*', 0, 0, 0, 1, 1)

CREATE FUNCTION FUBConcatenateSplitString 
(
	@pString VARCHAR (8000), @pDelimiter CHAR (1), 
	@pLeftConcat VARCHAR (MAX), @pRightConcat VARCHAR (MAX), 
	@pClearLeft BIT, @pClearRight BIT,
	@pIncludeLeftIndex BIT, @pIncludeRightIndex BIT, @pIncludeEqualsLeft BIT
) 
RETURNS 
	VARCHAR(MAX)
AS
BEGIN

	DECLARE @result VARCHAR (MAX)
	
	SET @result = ''

	SELECT 
		@result = @result + 
			@pLeftConcat +  
			CASE @pIncludeLeftIndex
				WHEN 1 THEN CONVERT (VARCHAR (MAX), [index])
				ELSE ''
			END +
			CASE @pIncludeEqualsLeft
				WHEN 1 THEN ' = '
				ELSE ''
			END +
			tempItem + 
			CASE @pIncludeRightIndex
				WHEN 1 THEN CONVERT (VARCHAR (MAX), [index])
				ELSE ''
			END +
			@pRightConcat 
	FROM 
		dbo.FUBSplitString (@pString, @pDelimiter) 

	IF @pClearLeft = 1
	BEGIN
		SET @result = SUBSTRING (@result, LEN (@pLeftConcat), LEN (@result) - LEN (@pLeftConcat))
	END
	
	IF @pClearRight = 1
	BEGIN
		SET @result = SUBSTRING (@result, 0, LEN (@result) - LEN (@pRightConcat))
	END

	RETURN @result

END